﻿using System;
using System.Collections.Generic;
using System.Text;
using Windvoice.Album.Model;
using System.Configuration;
using System.Data;
using Windvoice.Util;

namespace Windvoice.Album.DataAccess
{
    public class AlbumDAL
    {
        private string ConnectionString = ConfigurationManager.ConnectionStrings["windvoice"].ToString();

        public int Album_Insert(AlbumInfo album)
        {
            string command = "INSERT INTO [Album] ([albumname],[description],[prefaceid],[createtime],[updatetime]) VALUES ('{0}','{1}',{2},'{3}','{4}')";
            command = String.Format(command,album.AlbumName,album.Description,album.PrefaceID,album.CreateTime,album.UpdateTime);
            return AccessHelper.ExecuteNonQuery(ConnectionString,command);
        }

        public void Album_Delete(int AlbumID)
        {
            string command1 = "DELETE FROM [Album] WHERE [id]=" + AlbumID.ToString();
            string command2 = "DELETE FROM [Photo] WHERE [albumid]=" + AlbumID.ToString();

            AccessHelper.ExecuteNonQuery(ConnectionString, command1);
            AccessHelper.ExecuteNonQuery(ConnectionString, command2);
        }

        public int Album_Update(AlbumInfo album)
        {
            string command = "UPDATE [Album] SET [albumname]='{0}',[description]='{1}',[prefaceid]={2},[createtime]='{3}',[updatetime]='{4}' WHERE [id]={5}";
            command = String.Format(command, album.AlbumName,album.Description,album.PrefaceID,album.CreateTime,DateTime.Now,album.ID);
            return AccessHelper.ExecuteNonQuery(ConnectionString,command);
        }

        public int Album_ChangePreface(int AlbumID, int PrefaceID)
        {
            string command = "UPDATE [Album] SET [prefaceid]={0},[updatetime]='{1}' WHERE [id]={2}";
            command = String.Format(command,PrefaceID,DateTime.Now,AlbumID);
            return AccessHelper.ExecuteNonQuery(ConnectionString,command);
        }

        public AlbumInfo Album_Get(int AlbumID)
        {
            string command = "SELECT [id],[albumname],[description],[prefaceid],[createtime],[updatetime] FROM [Album] WHERE [id]="+AlbumID.ToString();
            DataTable dt = AccessHelper.ExecuteDataSet(ConnectionString, command).Tables[0];
            if (dt.Rows.Count > 0) return new AlbumInfo(dt.Rows[0]);
            else return null;
        }

        public AlbumInfo Album_Get(int AlbumID, out DataTable PhotoList)
        {
            string command = "SELECT [id],[albumname],[description],[prefaceid],[createtime],[updatetime] FROM [Album] WHERE [id]=" + AlbumID.ToString();
            DataTable dt = AccessHelper.ExecuteDataSet(ConnectionString, command).Tables[0];

            if (dt.Rows.Count > 0)
            {
                PhotoList = new PhotoDAL().Photo_GetList(AlbumID);
                return new AlbumInfo(dt.Rows[0]);
            }
            else
            {
                PhotoList = new DataTable();
                return null;
            } 
        }

        public DataTable Album_GetList()
        {
            string command = "SELECT [id],[albumname],[description],[prefaceid],[createtime],[updatetime] FROM [Album]";
            return AccessHelper.ExecuteDataSet(ConnectionString, command).Tables[0];
        }


    }
}
